package com.youmu.sichuansfc.util;


import com.youmu.sichuansfc.domain.YoumuFundManager;
import com.youmu.sichuansfc.domain.pojo.CompanyCountPojo;
import com.youmu.sichuansfc.domain.pojo.FundUserPojo;
import com.youmu.sichuansfc.domain.pojo.SearchYoumuFundManagerFund;
import org.apache.log4j.Logger;
import org.apache.poi.ss.usermodel.BorderStyle;
import org.apache.poi.xssf.usermodel.*;

import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.io.*;
import java.net.URLEncoder;
import java.text.SimpleDateFormat;
import java.util.List;


public class ReadEXCEL {

    private Logger logger = Logger.getLogger(this.getClass());

    /**
     *  导出基金管理人信息
     * @param request
     * @param response
     * @param list
     * @return
     * @throws Exception
     */
    public String exportManagerExcel(HttpServletRequest request,
                                     HttpServletResponse response,
                                     List<YoumuFundManager> list) throws Exception{
        String absFile = request.getRealPath("/excel/fund_manager.xlsx");
        XSSFWorkbook wb = returnWorkBookGivenFileHandle(absFile);
        XSSFSheet sheet1 = wb.getSheetAt(0);

        //填写商品数据
        XSSFCellStyle cellStyle = wb.createCellStyle();
        cellStyle.setBorderBottom(BorderStyle.THIN);
        cellStyle.setBorderLeft(BorderStyle.THIN);
        cellStyle.setBorderTop(BorderStyle.THIN);
        cellStyle.setBorderRight(BorderStyle.THIN);
        int grow = 1;
        int counter = 1;
        for(YoumuFundManager manager : list){

            XSSFRow row = createRow(sheet1, grow++);
            XSSFCell cell = row.createCell((short) 0);
            cell.setCellValue(counter++);

            cell = row.createCell(1);//私募管理人名称
            cell.setCellValue(manager.getManagerName());
            cell.setCellStyle(cellStyle);

            cell = row.createCell(2);//注册资本（万元）
            cell.setCellValue(manager.getRegisterMoney());
            cell.setCellStyle(cellStyle);

            cell = row.createCell(3);//管理规模（亿）
            cell.setCellValue(manager.getManagerMoney());
            cell.setCellStyle(cellStyle);

            cell = row.createCell(4);//基金支数
            cell.setCellValue(manager.getFundCount());
            cell.setCellStyle(cellStyle);

            String typeTxt = "";
            if(manager.getInvestType() == 1){
                typeTxt = "投资基金管理人";
            }else if(manager.getInvestType() == 2){
                typeTxt = "股权基金管理人";
            }else if(manager.getInvestType() == 3){
                typeTxt = "其他基金管理人";
            }
            cell = row.createCell(5);//投资类型
            cell.setCellValue(typeTxt);
            cell.setCellStyle(cellStyle);

            cell = row.createCell(6);//报送日期
            cell.setCellValue(new SimpleDateFormat("yyyy-MM-dd").format(manager.getAddTime()));
            cell.setCellStyle(cellStyle);

        }

        //保存地址
        String rpath = "/excel/fund_manager_"+System.currentTimeMillis()+".xlsx";
        String filePath = request.getRealPath(rpath);
        saveExcel(wb, filePath);

        logger.info("rpath : " + rpath);
        rpath = request.getContextPath() + rpath;
        logger.info("context + rpath : " + rpath);

        response.setContentType("application/vnd.ms-excel");
        response.setCharacterEncoding("utf-8");
        //这里对文件名进行编码，保证下载时汉字显示正常
        String fileName = URLEncoder.encode("基金管理人excel数据.xlsx", "utf-8");
        //Content-disposition属性设置成以附件方式进行下载
        response.setHeader("Content-disposition", "attachment;filename="
                + fileName);
        OutputStream os = response.getOutputStream();
        wb.write(os);
        os.flush();
        os.close();

        return rpath;
    }

    /**
     *  导出基金信息
     * @param request
     * @param response
     * @param list
     * @return
     * @throws Exception
     */
    public String exportManagerFundExcel(HttpServletRequest request,
                                     HttpServletResponse response,
                                     List<SearchYoumuFundManagerFund> list) throws Exception{
        String absFile = request.getRealPath("/excel/fund_manager_fund.xlsx");
        XSSFWorkbook wb = returnWorkBookGivenFileHandle(absFile);
        XSSFSheet sheet1 = wb.getSheetAt(0);

        //填写商品数据
        XSSFCellStyle cellStyle = wb.createCellStyle();
        cellStyle.setBorderBottom(BorderStyle.THIN);
        cellStyle.setBorderLeft(BorderStyle.THIN);
        cellStyle.setBorderTop(BorderStyle.THIN);
        cellStyle.setBorderRight(BorderStyle.THIN);
        int grow = 1;
        int counter = 1;
        for(SearchYoumuFundManagerFund fund : list){

            XSSFRow row = createRow(sheet1, grow++);
            XSSFCell cell = row.createCell((short) 0);
            cell.setCellValue(counter++);
            cell.setCellStyle(cellStyle);


            cell = row.createCell(1);//基金名称
            cell.setCellValue(fund.getFundName());
            cell.setCellStyle(cellStyle);

            cell = row.createCell(2);//私募管理人名称
            cell.setCellValue(fund.getManagerName());
            cell.setCellStyle(cellStyle);

            cell = row.createCell(3);//组织形式
            String[] organizaTye2 = {"其它","契约型","公司型","合伙型"};

            cell.setCellValue(organizaTye2[fund.getOrganizaTye()]);
            cell.setCellStyle(cellStyle);

            cell = row.createCell(4);//基金类型
            String[] fundType2 = {"","私募证券投资基金","私募股权、创业投资基金","其他投资基金"};
            cell.setCellValue(fundType2[fund.getFundType()]);
            cell.setCellStyle(cellStyle);

            cell = row.createCell(5);//托管人
            cell.setCellValue(fund.getTrusteeshipUser());
            cell.setCellStyle(cellStyle);

            cell = row.createCell(6);//报送日期
            cell.setCellValue(new SimpleDateFormat("yyyy-MM-dd").format(fund.getAddTime()));
            cell.setCellStyle(cellStyle);

        }

        //保存地址
        String rpath = "/excel/fund_manager_fund"+System.currentTimeMillis()+".xlsx";
        String filePath = request.getRealPath(rpath);
        saveExcel(wb, filePath);

        logger.info("rpath : " + rpath);
        rpath = request.getContextPath() + rpath;
        logger.info("context + rpath : " + rpath);

        response.setContentType("application/vnd.ms-excel");
        response.setCharacterEncoding("utf-8");
        //这里对文件名进行编码，保证下载时汉字显示正常
        String fileName = URLEncoder.encode("基金excel数据.xlsx", "utf-8");
        //Content-disposition属性设置成以附件方式进行下载
        response.setHeader("Content-disposition", "attachment;filename="
                + fileName);
        OutputStream os = response.getOutputStream();
        wb.write(os);
        os.flush();
        os.close();

        return rpath;
    }


    /**
     *  导出私募管理人账号信息
     * @param request
     * @param response
     * @param list
     * @return
     * @throws Exception
     */
    public String exportManagerUserExcel(HttpServletRequest request,
                                         HttpServletResponse response,
                                         List<FundUserPojo> list) throws Exception{
        String absFile = request.getRealPath("/excel/fund_manager_user.xlsx");
        XSSFWorkbook wb = returnWorkBookGivenFileHandle(absFile);
        XSSFSheet sheet1 = wb.getSheetAt(0);

        //填写商品数据

        XSSFCellStyle cellStyle = wb.createCellStyle();
        cellStyle.setBorderBottom(BorderStyle.THIN);
        cellStyle.setBorderLeft(BorderStyle.THIN);
        cellStyle.setBorderTop(BorderStyle.THIN);
        cellStyle.setBorderRight(BorderStyle.THIN);
        int grow = 1;
        int counter = 1;
        for(FundUserPojo user : list){

            XSSFRow row = createRow(sheet1, grow++);
            XSSFCell cell = row.createCell((short) 0);
            cell.setCellValue(counter++);
            cell.setCellStyle(cellStyle);


            cell = row.createCell(1);//私募管理人名称
            cell.setCellValue(user.getManagerName());
            cell.setCellStyle(cellStyle);

            cell = row.createCell(2);//联系人姓名
            cell.setCellValue(user.getContactName());
            cell.setCellStyle(cellStyle);

            cell = row.createCell(3);//联系人电话
            cell.setCellValue(user.getPhone());
            cell.setCellStyle(cellStyle);

            cell = row.createCell(4);//联系人邮箱
            cell.setCellValue(user.getContactEmail());
            cell.setCellStyle(cellStyle);

            cell = row.createCell(5);//密码
            cell.setCellValue(user.getPassword());
            cell.setCellStyle(cellStyle);

            cell = row.createCell(6);//创建日期
            cell.setCellValue(new SimpleDateFormat("yyyy-MM-dd").format(user.getAddTime()));
            cell.setCellStyle(cellStyle);

        }

        //保存地址
        String rpath = "/excel/fund_manager_user_"+System.currentTimeMillis()+".xlsx";
        String filePath = request.getRealPath(rpath);
        saveExcel(wb, filePath);

        logger.info("rpath : " + rpath);
        rpath = request.getContextPath() + rpath;
        logger.info("context + rpath : " + rpath);

        response.setContentType("application/vnd.ms-excel");
        response.setCharacterEncoding("utf-8");
        //这里对文件名进行编码，保证下载时汉字显示正常
        String fileName = URLEncoder.encode("私募管理人excel数据.xlsx", "utf-8");
        //Content-disposition属性设置成以附件方式进行下载
        response.setHeader("Content-disposition", "attachment;filename="
                + fileName);
        OutputStream os = response.getOutputStream();
        wb.write(os);
        os.flush();
        os.close();

        return rpath;
    }


    /**
     *  导出company公司类型分类数据
     * @param request
     * @param response
     * @param list
     * @return
     * @throws Exception
     */
    public String exportManagerTypeExcel(HttpServletRequest request,
                                         HttpServletResponse response,
                                        List<CompanyCountPojo> list) throws Exception{
      String absFile = request.getRealPath("/excel/company_type.xlsx");
        XSSFWorkbook wb = returnWorkBookGivenFileHandle(absFile);
        XSSFSheet sheet1 = wb.getSheetAt(0);

//        私募基金管理人类型	私募基金管理人数量  机构类型: 0 其它； 1 股权； 2证券; 3 投资
//        全部	0
//        私募证券管理人	0
//        私募股权管理人	0
//        创业投资管理人	0
//        其他投资管理人	0
        int[] typeIndex = {5, 3, 2, 4};
        int sum = 0;
        XSSFCellStyle cellStyle = wb.createCellStyle();
        cellStyle.setBorderBottom(BorderStyle.THIN);
        cellStyle.setBorderLeft(BorderStyle.THIN);
        cellStyle.setBorderTop(BorderStyle.THIN);
        cellStyle.setBorderRight(BorderStyle.THIN);
        for (CompanyCountPojo companyCountPojo : list){
            if (companyCountPojo.getCompanyType() == null)
                continue;
            XSSFRow row = sheet1.getRow(typeIndex[companyCountPojo.getCompanyType()]);
            XSSFCell cell = row.createCell((short) 1);
            cell.setCellValue(companyCountPojo.getCount());
            cell.setCellStyle(cellStyle);
            sum += companyCountPojo.getCount();
        }
        XSSFRow row = sheet1.getRow(1);
        XSSFCell cell = row.getCell((short) 1);
        cell.setCellValue(sum);
        cell.setCellStyle(cellStyle);

        //保存地址
        String rpath = "/excel/company_type"+System.currentTimeMillis()+".xlsx";
        String filePath = request.getRealPath(rpath);
        saveExcel(wb, filePath);

        logger.info("rpath : " + rpath);
        rpath = request.getContextPath() + rpath;
        logger.info("context + rpath : " + rpath);

        response.setContentType("application/vnd.ms-excel");
        response.setCharacterEncoding("utf-8");
        //这里对文件名进行编码，保证下载时汉字显示正常
        String fileName = URLEncoder.encode("管理人类型excel数据.xlsx", "utf-8");
        //Content-disposition属性设置成以附件方式进行下载
        response.setHeader("Content-disposition", "attachment;filename="
                + fileName);
        OutputStream os = response.getOutputStream();
        wb.write(os);
        os.flush();
        os.close();

        return rpath;
   }

    private XSSFWorkbook returnWorkBookGivenFileHandle(String excelPath) {
        XSSFWorkbook wb = null;
        FileInputStream fis = null;
        File f = new File(excelPath);
        try {
            if (f != null) {
                fis = new FileInputStream(f);
                wb = new XSSFWorkbook(fis);
            }
        } catch (Exception e) {
            return null;
        } finally {
            if (fis != null) {
                try {
                    fis.close();
                } catch (IOException e) {
                    e.printStackTrace();
                }
            }
        }
        return wb;
    }
    private void saveExcel(XSSFWorkbook wb, String newPath) {
        FileOutputStream fileOut;
        try {
            fileOut = new FileOutputStream(newPath);
            wb.write(fileOut);
            fileOut.close();
        } catch (FileNotFoundException e) {
            e.printStackTrace();
        } catch (IOException e) {
            e.printStackTrace();
        }

    }
    /**
     * 找到需要插入的行数，并新建一个POI的row对象
     *
     * @param sheet
     * @param rowIndex
     * @return
     */
    private XSSFRow createRow(XSSFSheet sheet, Integer rowIndex) {
        XSSFRow row = null;
        if (sheet.getRow(rowIndex) != null) {
            int lastRowNo = sheet.getLastRowNum();
            sheet.shiftRows(rowIndex, lastRowNo, 1);
        }
        row = sheet.createRow(rowIndex);
        return row;
    }

}
